﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CorsatAdminApp.Library.TempTaskClasses;
using System.Data.SqlClient;

namespace CorsatAdminApp.Library.DataManager
{
    public static partial class Main
    {
        public static void George_Test()
        {

        }
        
        #region Public Methods

        public static List<CommonItem> GetAllCategories()
        {
            command.CommandText = "SP_GetAllCategories";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Clear();

            adapter.SelectCommand = command;

            DataSet dataSet = new DataSet();

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                CloseConnection();
            }

            if (dataSet != null && dataSet.Tables[0].Rows.Count > 0)
            {
                return ConvertToCommonItemList(dataSet.Tables[0]);
            }
            else
                return null;

        }

        public static List<Item> GetCategoryItems(int CategoryId)
        {

            command.Connection = connection;
            command.CommandText = "SP_GetCategoryItems";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Clear();

            if (CategoryId == 0)
                command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = DBNull.Value;
            else
                command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = CategoryId;

            adapter.SelectCommand = command;

            DataSet dataSet = new DataSet();

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                CloseConnection();
            }

            if (dataSet != null && dataSet.Tables[0].Rows.Count > 0)
            {
                return ConvertToItemList(dataSet.Tables[0]);
            }
            else
                return null;
        }

        public static bool DeleteItem(int ItemId)
        {
            command.CommandText = "SP_DeleteItem";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Clear();

            command.Parameters.Add("@ItemId", SqlDbType.Int).Value = ItemId;

            try
            {
                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();
                return true;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return false;
            }

        }

        public static bool UpdateItem(Item _item)
        {

            command.CommandText = "SP_UpdateItem";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Clear();


            command.Parameters.Add("@ItemId", SqlDbType.Int).Value = _item.ItemId;
            command.Parameters.Add("@ItemName", SqlDbType.NVarChar).Value = _item.ItemName;
            command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = _item.ItemDescription;

            if (_item.ParentItem.HasValue)
                command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = _item.ParentItem.Value.ItemId;
            else
                command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;
            
            try
            {
                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();
                return true;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return false;
            }
        }

        public static bool InsertItem(Item _item)
        {

            command.CommandText = "SP_InsertItem";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Clear();

            command.Parameters.Add("@ItemName", SqlDbType.NVarChar).Value = _item.ItemName;
            command.Parameters.Add("@Description", SqlDbType.NVarChar).Value = _item.ItemDescription;
            command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = _item.ItemCategory.ItemId;

            if (_item.ParentItem.HasValue)
                command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = _item.ParentItem.Value.ItemId;
            else
                command.Parameters.Add("@ParentItemId", SqlDbType.Int).Value = DBNull.Value;

            try
            {
                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();
                return true;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return false;
            }
        }

        #endregion

        #region Private Methods

        private static List<CommonItem> ConvertToCommonItemList(DataTable dataTable)
        {
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                List<CommonItem> commonItemList = new List<CommonItem>();
                foreach (DataRow dr in dataTable.Rows)
                {
                    CommonItem tempItem = new CommonItem();
                    tempItem.ItemId = Convert.ToInt32(dr["ID"]);
                    tempItem.ItemName = Convert.ToString(dr["Name"]);

                    commonItemList.Add(tempItem);
                }
                return commonItemList;
            }
            else
                return null;
        }

        private static List<Item> ConvertToItemList(DataTable dataTable)
        {
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                List<Item> ItemList = new List<Item>();

                foreach (DataRow dr in dataTable.Rows)
                {
                    Item tempItem = new Item();
                    tempItem.ItemId = Convert.ToInt32(dr["ItemId"]);
                    tempItem.ItemName = Convert.ToString(dr["ItemName"]);
                    tempItem.ItemDescription = Convert.ToString(dr["Description"]);

                    CommonItem tempItemCategory = new CommonItem();
                    tempItemCategory.ItemId = Convert.ToInt32(dr["CategoryId"]);
                    tempItemCategory.ItemName = Convert.ToString(dr["CategoryName"]);

                    tempItem.ItemCategory = tempItemCategory;

                    if (dr["ParentItemId"] != DBNull.Value)
                    {
                        CommonItem tempHeaderItem = new CommonItem();
                        tempHeaderItem.ItemId = Convert.ToInt32(dr["ParentItemId"]);
                        tempHeaderItem.ItemName = Convert.ToString(dr["ParentItemName"]);

                        tempItem.ParentItem = tempHeaderItem;
                    }
                    else
                        tempItem.ParentItem = null;

                    ItemList.Add(tempItem);
                }

                return ItemList;
            }
            else
                return null;
        }

        #endregion

    }
}
